package com.elite;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.net.SocketException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.commons.net.ftp.FTPClient;  
import org.apache.commons.net.ftp.FTPClientConfig;
import org.apache.commons.net.ftp.FTPFile;  
import org.apache.commons.net.ftp.FTPReply;  
import org.apache.commons.net.ftp.FTP;  
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

public class test {
	private static final Log log=LogFactory.getLog(test.class);
	public static void main(String args[]) throws SocketException, IOException{
//		FTPClient ftp=GetFtp("127.0.0.1",21,"tony","");
//	     File file = new File("e:\\123\\cc.xlsx");
//	      FileOutputStream fos = new FileOutputStream(file);
//	      ftp.retrieveFile("a/" + "cc.xlsx", fos);
		FileInputStream is = new FileInputStream(new File("E:\\新契约回访数据.xls")); 
		
//		 ByteArrayOutputStream byteOS = new ByteArrayOutputStream(); 
//		byte[] by = new byte[512];  
//		int t = is.read(by,0,by.length); 
//		 while(t>0){   byteOS.write(by, 0, 512);  //这里别写成t，写够512，呵呵，SB的方法对付SB的java API
//		 t = is.read(by,0,by.length);  
//		} 
//		 byteOS.close();  
//		InputStream byteIS = new ByteArrayInputStream(byteOS.toByteArray());  
		
		
		
		
        HSSFWorkbook wb=new HSSFWorkbook(is);
        HSSFSheet childSheet = wb.getSheetAt(0);   
        int rowNum=childSheet.getLastRowNum()+1;
        
        int size=rowNum-1;
    
        String[][] data=new String[size][60];
        SimpleDateFormat DateFormat=new SimpleDateFormat("yyyy-MM-dd"); 
        for(int j=1;j<rowNum;j++){   
            HSSFRow row = childSheet.getRow(j);    
            int cellNum=row.getLastCellNum(); 
            for(int k=0;k<cellNum;k++)   
            {   
            	HSSFCell cell=row.getCell((short) k);
        		if(cell==null){
        			data[j-1][k]="";
        		}else if(cell.getCellType()==3){
            		data[j-1][k]="";
            	}else if(cell.getCellType()==1){
                		String a=cell.toString();
            			data[j-1][k]=cell.toString();
            	}else if(cell.getCellType()==0){
            		if(HSSFDateUtil.isCellDateFormatted(cell)||k==16||k==29||k==34||k==35||k==36||k==37||k==45||k==46)
                    {
                    	data[j-1][k]= DateFormat.format(cell.getDateCellValue());
                    	if(data[j-1][k].length()>19){
                    	  data[j-1][k]= DateFormat.format(cell.getDateCellValue()).substring(0, 19);
                    	}
                    }else{
                    		double a=cell.getNumericCellValue();
                    		data[j-1][k]=a+"";
                    }
            	}else if(HSSFCell.CELL_TYPE_BLANK == cell.getCellType()){
            		data[j-1][k]="";
            	}
        		  log.info(j-1+"-"+k+":"+data[j-1][k]);
            }     
        }  
        int aaa=0;
        for(int i1=0;i1<data.length;i1++){
        	String MANAGECOM=data[i1][2];  //管理机构C
        	String MANAGECOMID=data[i1][3];   //机构代码D
        	String MANAGECOMNAME=data[i1][4];    //机构名称E
        	String SALECHNL=data[i1][5];              //销售渠道F
        	String YBTFLAG=data[i1][6];   //银保通标识G
        	String AGENTCOMCODE=data[i1][7];  // 代理机构代码H
        	String AGENTCOMNAME=data[i1][8]; // 代理机构I
        	String CONTNO=data[i1][10];   //保单号码K
        	String CONTSTATE=data[i1][11]; //保单状态L
        	String APPNTNAME=data[i1][13];   // 投保人姓名N
        	String APPNTNO=data[i1][14];    //投保客户号O
        	String APPNTSEX=data[i1][15];        //投保人性别P
        	String APPNTBIRTHDAY=data[i1][16];   //出生日期-投保人信息Q
        	if(APPNTBIRTHDAY!=null&&APPNTBIRTHDAY.length()>3){
        		APPNTBIRTHDAY="to_date('"+APPNTBIRTHDAY+"','yyyy-mm-dd')";
        	}else{
        		APPNTBIRTHDAY="''";
        	}
        	String APPNTAGE=data[i1][17];   //投保人年龄R
        	String APPNTIDTYPE=data[i1][18];    //证件类型S
        	String APPNTIDNO=data[i1][19];     //证件号码T
        	String APPNTMOBILE=data[i1][20];    //移动电话U
        	String APPNTPHONE=data[i1][21];    //固定电话V
        	String APPNTPHONE1=data[i1][22];    //固定电话W
        	String mail=data[i1][23];    //幽香X
        	String APPNTADDRESS=data[i1][24];   //联系地址Y
        	String APPNTZIPCODE=data[i1][25];   //邮政编码Z
        	String INUSERDNAME=data[i1][26];    // 被保人姓名AA
        	String INSUREDNO=data[i1][27];    //被保客户号AB
        	String INSUREDSET=data[i1][28];   //被保人性别AC
        	String INSUREDBIRTHDAY=data[i1][29];   //出生日期AD
        	if(INSUREDBIRTHDAY!=null&&INSUREDBIRTHDAY.length()>3){
        		INSUREDBIRTHDAY="to_date('"+INSUREDBIRTHDAY+"','yyyy-mm-dd')";
        	}else{
        		INSUREDBIRTHDAY="''";
        	}
          	String INSUERDAGE=data[i1][30];   //被保人年龄AE
        	String INSUREDIDTYPE=data[i1][31];   //被保人证件类型AF
        	String INSUERDIDNO=data[i1][32];   //被保人证件号码AG
        	String RELATIONTOAPPNT=data[i1][33];   //与投保人关系AH
        	String SIGEDATE=data[i1][34];   //签单日期AI
        	if(SIGEDATE!=null&&SIGEDATE.length()>3){
        		SIGEDATE="to_date('"+SIGEDATE+"','yyyy-mm-dd')";
        	}else{
        		SIGEDATE="''";
        	}
        	String CVALIDATE=data[i1][35];   //生效日期AJ
        	if(CVALIDATE!=null&&CVALIDATE.length()>3){
        		CVALIDATE="to_date('"+CVALIDATE+"','yyyy-mm-dd')";
        	}else{
        		CVALIDATE="''";
        	}
        	String CUSTOMGETPOLDATE=data[i1][36];   //客户签收日期AK
        	if(CUSTOMGETPOLDATE!=null&&CUSTOMGETPOLDATE.length()>3){
        		CUSTOMGETPOLDATE="to_date('"+CUSTOMGETPOLDATE+"','yyyy-mm-dd')";
        	}else{
        		CUSTOMGETPOLDATE="''";
        	}
        	String GETPOLDATE=data[i1][37];   //回单日期AL
        	if(GETPOLDATE!=null&&GETPOLDATE.length()>3){
        		GETPOLDATE="to_date('"+GETPOLDATE+"','yyyy-mm-dd')";
        	}else{
        		GETPOLDATE="''";
        	}
        	String RISKCODE=data[i1][38];   //险种编码AM
        	String RISKNAME=data[i1][39];   //险种名称AN
        	String AGENTGROUP=data[i1][51];   //代理人部组号AZ
        	String BRAANCHCODE=data[i1][52];   //展业机构code BA
        	String BRAANCHTYPE=data[i1][53];   //展业机构BB
        	String AGENTCODE=data[i1][54];   //代理人代码BC
        	String AGENTNAME=data[i1][55];   //代理人姓名BD
        	String AGENTMOBILE=data[i1][56];   //移动电话BE
        	String cddate=data[i1][1];   //抽档日期-创建时间-----------------
        	if(cddate!=null&&cddate.length()>3){
        		cddate="to_date('"+cddate+"','yyyy-mm-dd')";
        	}else{
        		cddate="''";
        	}
        	String YWTYPE=data[i1][9];   //业务类型J
        	String YIPFLAG=data[i1][12];   //VIP标识M
        	String BFTOTAL=data[i1][40];   //保费合计AO
        	String ZXPREMIUM=data[i1][41];   //主险保费AP
        	String ZXINSURED=data[i1][42];   //主险保额AQ
        	String FJXPREMIUM=data[i1][43];   //附加险保费总额AR
        	String GRADE=data[i1][44];   //档次AS
        	String BXZQDATE=data[i1][45];   //保险止期AT
        	if(BXZQDATE!=null&&BXZQDATE.length()>3){
        		BXZQDATE="to_date('"+BXZQDATE+"','yyyy-mm-dd')";
        	}else{
        		BXZQDATE="''";
        	}
        	String PAYPERIOD=data[i1][46];   //交费年期AU
        	if(PAYPERIOD!=null&&PAYPERIOD.length()>3){
        		PAYPERIOD="to_date('"+PAYPERIOD+"','yyyy-mm-dd')";
        	}else{
        		PAYPERIOD="''";
        	}
        	String PAYTYPE=data[i1][47];   //交费形式AV
        	String SQJFXS=data[i1][48];   //首期交费形式AW
        	String XQJFXS=data[i1][49];   //续期交费形式AX
        	String BBAGE=data[i1][50];   //保至被保险人年龄AY
        	String CREATEBY="001";   //创建人----------------------------
        	String OBJECTVIE_GUID="";   //OBJECTVIE_GUID---------------------
        	String FLAG="1";   //是否生成外呼任务，1是生成0是未生成-------------------
//        	String MODIFIEDDATE=data[i1][7];   //修改时间
        	
        	int judge1=0;
        	String sql11="";
        	String sql12="";
//            	sql11="insert into XF_VISIT(" +
//            			"MANAGECOM,MANAGECOMID,MANAGECOMNAME,SALECHNL,YBTFLAG,AGENTCOMNAME," +
//            			"CONTNO,CONTSTATE,APPNTNAME,APPNTNO,APPNTSEX,APPNTBIRTHDAY," +
//            			"APPNTAGE,APPNTIDTYPE,APPNTIDNO,APPNTMOBILE,APPNTPHONE,APPNTADDRESS," +
//            			"APPNTZIPCODE,INUSERDNAME,INSUREDNO,INSUREDSET,INSUREDBIRTHDAY,INSUERDAGE," +
//            			"INSUREDIDTYPE,INSUERDIDNO,RELATIONTOAPPNT,SIGEDATE, CVALIDATE,CUSTOMGETPOLDATE," +
//            			"GETPOLDATE,RISKCODE,RISKNAME,AGENTGROUP,BRAANCHTYPE,AGENTCODE," +
//            			"AGENTNAME,AGENTMOBILE,CREATEDATE,YWTYPE,YIPFLAG,BFTOTAL," +
//            			"ZXPREMIUM,ZXINSURED,FJXPREMIUM,GRADE,BXZQDATE,PAYPERIOD," +
//            			"PAYTYPE,SQJFXS,XQJFXS,BBAGE,CREATEBY,OBJECTVIE_GUID,FLAG,MODIFIEDDATE) Values" +
//            			"('"+MANAGECOM+"','"+MANAGECOMID+"','"+MANAGECOMNAME+"','"+SALECHNL+"','"+YBTFLAG+"','"+AGENTCOMNAME+
//            			"','"+CONTNO+"','"+CONTSTATE+"','"+APPNTNAME+"','"+APPNTNO+"','"+APPNTSEX+"',"+APPNTBIRTHDAY+
//            			",'"+APPNTAGE+"','"+APPNTIDTYPE+"','"+APPNTIDNO+"','"+APPNTMOBILE+"','"+APPNTPHONE+"','"+APPNTADDRESS+
//            			"','"+APPNTZIPCODE+"','"+INUSERDNAME+"','"+INSUREDNO+"'," +"'"+INSUREDSET+"',"+INSUREDBIRTHDAY+",'"+INSUERDAGE+
//            			"','"+INSUREDIDTYPE+"','"+INSUERDIDNO+"','"+RELATIONTOAPPNT+"'," +SIGEDATE+","+CVALIDATE+","+CUSTOMGETPOLDATE+
//            			","+GETPOLDATE+",'"+RISKCODE+"','"+RISKNAME+"','"+AGENTGROUP+"','"+BRAANCHTYPE+"','"+AGENTCODE+
//            			"','"+AGENTNAME+"','"+AGENTMOBILE+"',"+CREATEDATE+",'"+YWTYPE+"','"+YIPFLAG+"','"+BFTOTAL+
//            			"','"+ZXPREMIUM+"','"+ZXINSURED+"','"+FJXPREMIUM+"','"+GRADE+"',"+BXZQDATE+","+PAYPERIOD+
//            			",'"+PAYTYPE+"','"+SQJFXS+"','"+XQJFXS+"','"+BBAGE+"','"+CREATEBY+"','"+OBJECTVIE_GUID+"',"+FLAG+",sysdate)";
//         
//            	sql12=" update XF_VISIT set MANAGECOM='"+MANAGECOM+"',MANAGECOMID='"+MANAGECOMID+"',MANAGECOMNAME='"+MANAGECOMNAME+"'," +
//            			"SALECHNL='"+SALECHNL+"', YBTFLAG='"+YBTFLAG+"',AGENTCOMNAME='"+AGENTCOMNAME+"',CONTSTATE='"+CONTSTATE+"'," +
//            					" APPNTNAME='"+APPNTNAME+"', APPNTNO='"+APPNTNO+"', APPNTSEX='"+APPNTSEX+"',APPNTBIRTHDAY="+APPNTBIRTHDAY+"," +
//            							"APPNTAGE='"+APPNTAGE+"',APPNTIDTYPE='"+APPNTIDTYPE+"',APPNTIDNO='"+APPNTIDNO+"',APPNTMOBILE='"+APPNTMOBILE+"',APPNTPHONE='"+APPNTPHONE+"'," +
//            									"APPNTADDRESS='"+APPNTADDRESS+"',APPNTZIPCODE='"+APPNTZIPCODE+"',INUSERDNAME='"+INUSERDNAME+"',INSUREDNO='"+INSUREDNO+"',INSUREDSET='"+INSUREDSET+"',INSUREDBIRTHDAY="+INSUREDBIRTHDAY+"," +
//            											"INSUERDAGE='"+INSUERDAGE+"',INSUREDIDTYPE='"+INSUREDIDTYPE+"',INSUERDIDNO='"+INSUERDIDNO+"',RELATIONTOAPPNT='"+RELATIONTOAPPNT+"',SIGEDATE="+SIGEDATE+",CVALIDATE="+CVALIDATE+",CUSTOMGETPOLDATE="+CUSTOMGETPOLDATE+",GETPOLDATE="+GETPOLDATE+"," +
//            													"RISKCODE='"+RISKCODE+"',RISKNAME='"+RISKNAME+"',AGENTGROUP='"+AGENTGROUP+"',BRAANCHTYPE='"+BRAANCHTYPE+"',AGENTCODE='"+AGENTCODE+"',AGENTNAME='"+AGENTNAME+"',AGENTMOBILE='"+AGENTMOBILE+"',CREATEDATE="+CREATEDATE+",YWTYPE='"+YWTYPE+"',YIPFLAG='"+YIPFLAG+"'," +
//            															"BFTOTAL='"+BFTOTAL+"',ZXPREMIUM='"+ZXPREMIUM+"',ZXINSURED='"+ZXINSURED+"',FJXPREMIUM='"+FJXPREMIUM+"',GRADE='"+GRADE+"',BXZQDATE="+BXZQDATE+",PAYPERIOD="+PAYPERIOD+",PAYTYPE='"+PAYTYPE+"'," +
//            																	"SQJFXS='"+SQJFXS+"',XQJFXS='"+XQJFXS+"',BBAGE='"+BBAGE+"',CREATEBY='"+CREATEBY+"',OBJECTVIE_GUID='"+OBJECTVIE_GUID+"',FLAG="+FLAG+",MODIFIEDDATE=sysdate where  CONTNO ='"+CONTNO+"'";
//       
////        log.info("更新SQL语句:"+sql11);
//        log.info("更新SQL语句:"+sql11);
//    
}
}
}